#!/usr/local/bin/python
# -*- coding: utf-8 -*-
from sqlite3 import Date


from sqlalchemy import Column, Integer, Float, and_, Text, Date
from sqlalchemy.orm import declarative_base

import init
import initLogger
import toolkit
from init import session


Base = declarative_base()

class IncomeStatementMonthlyAlchemy(Base):
    '''
    类功能：按照月份统计收支表合计数据对应数据库映射表
    '''

    __tablename__ = "income_statement_month"

    id = Column(Integer, primary_key=True, autoincrement=True, comment="数据表id值")
    date_time =Column(Date, comment="数字格式的年份和月份时间")
    year = Column(Integer, comment="数据归属年份")
    month = Column(Integer, comment="数据归属月份")
    data_type = Column(Text, comment="标记属于[收入-income]、[支出-disbursement]、[投资-investment]")
    current_month_value = Column(Float, comment="当前月金额")
    total_value = Column(Float, comment="累计金额")



    def __init__(self,
                 year=2022,
                 month=1,
                 dataType="",
                 currentMonthValue=0.0,
                 totalValue=0.0
                 ):
        self.date_time = toolkit.setTargetDate(year, month)
        self.year = year
        self.month = month
        self.data_type = dataType
        self.current_month_value = currentMonthValue
        self.total_value = totalValue


    def AddIncomeStatementItem(self):
        '''
        函数功能：添加一条数据
        :return:
        '''
        session.add(self)
        session.commit()

    def UpdateIncomeStatementtem(self):
        '''
        函数功能；更新一条数据
        :return:
        '''

    def SearchIncomeStatementByMonth(self, year, month):
        '''
        函数功能：查询指定月份的收支数据
        :param year:
        :param month:
        :return:
        '''

        incomeStatementInfo = session.query(IncomeStatementMonthlyAlchemy).filter(and_(IncomeStatementMonthlyAlchemy.year == year,
                                                                        IncomeStatementMonthlyAlchemy.month == month))\
                                                                        .all()
        return incomeStatementInfo


    def SearchIncomeStatementByDate(self, startDate, endDate):
        '''
        函数功能：以以起始日期（年、月）方式，查询月度收支表
        :param startDate:
        :param endDate:
        :return:
        '''
        incomeStatementInfo = session.query(IncomeStatementMonthlyAlchemy).filter(and_(IncomeStatementMonthlyAlchemy.date_time >= startDate,
                                                                        IncomeStatementMonthlyAlchemy.date_time <= endDate))\
                                                                        .all()
        return incomeStatementInfo

    def SearchIncomeStatementByYear(self, year):
        '''
        函数功能：查询指定年份各月的收支情况
        :param year:
        :return:
        '''
        incomeStatementInfo = session.query(IncomeStatementMonthlyAlchemy)\
                                     .filter(IncomeStatementMonthlyAlchemy.year==year)  \
                                     .all()
        return incomeStatementInfo



    def UpdateAllDatetimeDataTemp(self):
        '''
        函数功能：临时更新收支表数据,用于填充date_time字段的数据
        :return:
        '''

        incomeStatementInfoList = session.query(IncomeStatementMonthlyAlchemy).filter().all()

        for i in range(len(incomeStatementInfoList)):
            initLogger.logger.debug(i)
            incomeStatementInfoItem = incomeStatementInfoList[i]
            incomeStatementInfoItem.date_time = toolkit.setTargetDate(incomeStatementInfoItem.year,
                                                                      incomeStatementInfoItem.month)

        session.commit()
        return

    def ShowData(self):
        '''
        函数功能：展示读取出来的数据
        :return:
        '''
        pass

    def DelIncomeStatementByMonth(self, year, month):
        '''
        函数功能，删除指定月份的张博数据
        :param year:
        :param month:
        :return:
        '''

        deleteIncomeStatementInfo = session.query(IncomeStatementMonthlyAlchemy).filter(and_(IncomeStatementMonthlyAlchemy.year==year,
                                                                     IncomeStatementMonthlyAlchemy.month==month))
        deleteIncomeStatementInfo.delete()
        session.commit()


    def GetLatestIncomeValueByYear(self, year):
        '''
        函数功能：获取指定年度最新的收入情况
        :param year:
        :return:
        '''

        latestIncomeValueItem = session.query(IncomeStatementMonthlyAlchemy).filter(and_(IncomeStatementMonthlyAlchemy.year==year,
                                                                                       IncomeStatementMonthlyAlchemy.data_type=="收入"))\
                                                                          .order_by(IncomeStatementMonthlyAlchemy.date_time.desc()) \
                                                                          .first()


        if latestIncomeValueItem==None:
            return None

        return  latestIncomeValueItem.total_value

    def GetLatestExpenseValueByYear(self, year):
        '''
        函数功能：获取指定年度最新的支出情况
        :param year:
        :return:
        '''
        latestExpenseValueItem = session.query(IncomeStatementMonthlyAlchemy).filter(and_(IncomeStatementMonthlyAlchemy.year==year,
                                                                                       IncomeStatementMonthlyAlchemy.data_type=="支出"))\
                                                                          .order_by(IncomeStatementMonthlyAlchemy.date_time.desc()) \
                                                                          .first()
        if latestExpenseValueItem==None:
            return None

        return  latestExpenseValueItem.total_value

def main():
    incomeStatementAlchemy = IncomeStatementMonthlyAlchemy()
    incomeStatementAlchemy.UpdateAllDatetimeDataTemp()


    '''
    incomeStatementAlchemy = IncomeStatementMonthlyAlchemy(year=2022,
                                          month=7,
                                          dataType="income",
                                          currentMonthValue=100,
                                          totalValue=199
                                          )

    incomeStatementAlchemy.AddIncomeStatementItem()
    '''
#创建数据库表
IncomeStatementMonthlyAlchemy.metadata.create_all(init.engine)


if __name__ == '__main__':
    main()